package dao;
import java.sql.*;
import java.util.ArrayList;
import Shiti.Shopti;
import Shiti.Userti;

public class ShopDao {
    private final String driver = "com.mysql.cj.jdbc.Driver";
    // 获取mysql连接地址
    private final String url = "jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC";
    // 数据名称
    private final String username = "root";
    // 数据库密码
    private final String password = "root";
    // 获取一个数据的连接
    //查一个
    public  Shopti queryone(Shopti shopti){
        Shopti shopti1 =new Shopti();
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //要执行的SQL语句
            String sql ="select * from shop where id = ?";
            statement=conn.prepareStatement(sql);
            statement.setInt(1, shopti.getId());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            rs = statement.executeQuery();
            int id = 0;
            String name = null;
            String bus = null;
            while (rs.next()) {
                //获取‘学号’这列数据
                id = Integer.parseInt(rs.getString("id"));
                //获取‘姓名’这列数据
                name = rs.getString("name");
                //获取‘’这列数据
                bus= rs.getString("bus");
               shopti1 = new  Shopti(id,name,bus);
            }
            return shopti1;
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  null;

        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  null;

        } catch (Exception e2) {
            e2.printStackTrace();
            return  null;

        } finally { try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //查全部
    public ArrayList<Shopti>query(){
        ArrayList <Shopti> list =new ArrayList<Shopti>();
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        try {
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //要执行的SQL语句
            String sql ="select * from shop";
            statement=conn.prepareStatement(sql);
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            rs = statement.executeQuery();
            int id = 0;
            String name = null;
            String bus = null;
            while (rs.next()) {
                //获取‘学号’这列数据
                id = Integer.parseInt(rs.getString("id"));
                //获取‘姓名’这列数据
                name = rs.getString("name");
                //获取‘语文成绩’这列数据
                bus= rs.getString("bus");
                Shopti ss =new Shopti(id,name,bus);
                list.add(ss);
            }
            return list;
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            e.printStackTrace();
            return  null;

        } catch (SQLException e1) {
            //数据库连接失败异常处理
            e1.printStackTrace();
            return  null;

        } catch (Exception e2) {
            e2.printStackTrace();
            return  null;

        } finally { try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //添加
    public  boolean add(Shopti shopti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn= DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="insert into shop values (?,?,? )";
            statement=conn.prepareStatement(sql);
            statement.setInt(1,shopti.getId());
            statement.setString(2, shopti.getName());
            statement.setString(3,shopti.getBus());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //删除
    public  boolean dele(Shopti shopti){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="delete from shop where id = ?";
            statement=conn.prepareStatement(sql);
            statement.setInt(1,shopti.getId());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
    //改商家
    public  boolean changebus(Shopti shopti,String bus){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="update shop set bus =? where id = ?";
            statement=conn.prepareStatement(sql);
            statement.setString(1,bus);
            statement.setInt(2,shopti.getId());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //改密码
    public  boolean changename(Shopti shopti,String name){
        Connection conn = null;
        ResultSet rs=null;
        PreparedStatement statement=null;
        // 获取连接的一个状态
        try{
            Class.forName(driver);
            //getConnection()方法，连接MySQL数据库！
            conn=DriverManager.getConnection(url,username,password);
            //创建statement类对象，用来执行SQL语句！
            //要执行的SQL语句
            String sql ="update shop set name =? where id = ?";
            statement=conn.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,shopti.getId());
            //ResultSet类，用来存放获取的结果集！
            statement.execute();
            return true;
        }
        catch(ClassNotFoundException e){
            //数据库驱动类异常处理
            e.printStackTrace();
            return false;
        }
        catch(SQLException e1){
            //数据库连接失败异常处理
            e1.printStackTrace();
            return false;
        }
        catch(Exception e2){
            e2.printStackTrace();
            return false;
        }
        finally {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}
